﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Configuration; 
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace HoneyBadgersOfficePlayer
{
    public partial class SQLUtilities
    {
        public static SqlConnection SQLConnect()
        {
            string connectionString;
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
            connectionString = settings[1].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        public static SqlDataReader RunStoredProcedure(string procedureName, List<SqlParameter> parameterList)
        {
            SqlConnection connection = SQLUtilities.SQLConnect();
            SqlCommand taskCommand = new SqlCommand();
            SqlDataReader reader;
            taskCommand.CommandText = procedureName;
            taskCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameterList)
            {
                taskCommand.Parameters.Add(parameter);
            }

            taskCommand.Connection = connection;
            reader = taskCommand.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader;
            }
            else
            {
                return null;
            }
        }

        public static int RunStoredProcedure(string procedureName, List<SqlParameter> parameterList, SqlParameter returnParameter)
        {
            SqlConnection connection = SQLUtilities.SQLConnect();
            SqlCommand taskCommand = new SqlCommand("", connection);
            taskCommand.CommandText = procedureName;
            taskCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameterList)
            {
                taskCommand.Parameters.Add(parameter);
            }
            returnParameter.Direction = ParameterDirection.Output;
            taskCommand.Parameters.Add(returnParameter);

            taskCommand.ExecuteNonQuery();
            int result = (int)taskCommand.Parameters[returnParameter.ParameterName].Value;

            connection.Close();

            return result;
        }

        public static void CloseReader(SqlDataReader reader)
        {
            if (reader != null)
                reader.Close();
        }

        public static void CloseConnection(SqlConnection connection)
        {
            connection.Close();
        }
    }
}